import numpy as np
import xlrd
#from win32com.client import Dispatch

def generate_fips(stateid, countyid):
    stateid = str(int(float(stateid)))
    countyid = str(int(float(countyid)))
    if stateid == '' or countyid == '':
        print 'error in generate_fips' 
        return null
    if len(stateid) < 2:
        stateid = '0' + str(stateid)

    if len(countyid) < 2:
        countyid = '00' + str(countyid)
    elif len(countyid) < 3:
        countyid = '0' + str(countyid)

    #print temp
    return stateid, countyid, stateid + countyid

#--------------------------------------------------------------------------
#MAIN
if __name__ == "__main__":
    print '============='
    # open excel application module
    #xlsApp=Dispatch("Excel.Application")
    period = ['9293', '9394', '9495', '9596', '9697', '9798', '9899', '9900', '0001', '0102', '0203', '0304', '0405', '0506', '0607', '0708']
    #period = ['0102', '0203', '0304', '0405', '0506', '0607', '0708']
    year = range(1992, 2008)
    #year = range(2001, 2008)

    fileFloder = 'C:/_DATA/migration89_08/COUNTY Migration/'
    
    row = 0
    #state = ['al', 'ak', 'az', 'ar', 'ca', 'co', 'ct', 'de', 'fl', 'ga', 'hi', 'id', 'il', 'in', 'ia', 'ks', 'ky', 'la', 'me', 'md', 'ma', 'mi', 'mn', 'ms', 'mo', 'mt', 'ne', 'nv', 'nh', 'nj', 'nm', 'ny', 'nc', 'nd', 'oh', 'ok', 'or', 'pa', 'ri', 'sc', 'sd', 'tn', 'tx', 'ut', 'vt', 'va', 'wa', 'wv', 'wi', 'wy']
    state = ['dc']
    inout = ['i','o']
    #state = ['ak']
    period = ['9394']
    year = [1993]

    j = 0
    for y in year:
        for io in inout:
            if io == 'i':
                filePath = fileFloder + 'co' + period[j] + '/co' + period[j] + 'in_excel/co'
            else:
                filePath = fileFloder + 'co' + period[j] + '/co' + period[j] + 'out_excel/co'
                
            totalOutput = np.array([])
            
            for st in state:
                print y, st, io
                inputfile = filePath + period[j] + st + io +'.xls'

                xlsBook = xlrd.open_workbook(inputfile)
                xlsSheet = xlsBook.sheet_by_index(0)

                #output = np.array([])
                output = []
                NRows = xlsSheet.nrows
                for i in range(8, NRows):
                    temp = xlsSheet.row_values(i)
                    
                    ostate = str(int(temp[0]))
                    temp[0] = ostate
                    ocounty = '00'+str(int(temp[1]))
                    temp[1] = ocounty
                    dstate = str(int(temp[2]))
                    if len(dstate) < 2:
                        dstate = '0' + dstate
                    temp[2] = dstate
                    dcounty = str(int(temp[3]))
                    if len(dcounty) < 2:
                        dcounty = '00' + dcounty
                    elif len(dcounty) < 3:
                        dcounty = '0' + dcounty
                    temp[3] = dcounty
                    print temp
                    
                    if temp[6] == 'd':
                        continue
                    output.append([str(y), temp[0], temp[1], temp[0] + temp[1], temp[2], temp[3], temp[2] + temp[3],
                                   str(int(temp[6])), str(int(temp[7])), str(int(temp[8]))])
                    #output = np.append(output, generate_fips(temp[0], temp[1]))
                    #output = np.append(output, generate_fips(temp[2], str(temp[3])))
                    #output = np.append(output, [str(int(temp[6])), str(int(temp[7])), str(int(temp[8]))])
                    #output.append([xlsSheet.Cells(i,1), xlsSheet.Cells(i,2), xlsSheet.Cells(i,3), xlsSheet.Cells(i,4), xlsSheet.Cells(i,7), xlsSheet.Cells(i,8), xlsSheet.Cells(i,9)])

                totalOutput = np.append(totalOutput, output)
                #output = np.array(output)
                #print output
                #output.shape = (-1, 10)
                outputPath = inputfile[:-4] + '.csv'
                np.savetxt(outputPath, output, delimiter=',', fmt = '%s')
            outputPath = fileFloder + 'csv/u/dc_' + period[j] + io + '_1.csv'
            #totalOutput = np.array(totalOutput)
            totalOutput.shape = (-1, 10)
            np.savetxt(outputPath, totalOutput, delimiter=',', fmt = '%s')
        j += 1
            #np.savetxt(outputPath, totalOutput, delimiter=',', fmt = '%10.5f')
    print 'Done!'
        

